--日历排班
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_calendar_pu_tong')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_calendar_pu_tong
go

CREATE PROCEDURE dbo.bd_proc_schedule_calendar_pu_tong
	@user_serial_str varchar(max),-- 人员ID
	@calendar_id int,-- 日历ID
	@ip varchar(max),-- IP
	@operator varchar(max),-- 操作人
	@lx int  -- 类型 0 人员档案操作  不为0 其他操作
as
BEGIN

	-- 定义返回受影响行数结果
	declare @result int = 0
	-- 人员日历
	declare @userCalendarId int
	--日历年份
	declare @workYear varchar(max)
	-- 排班需要的
	-- 工作开始日期
	declare @workBeginDate varchar(max)
	-- 工作结束日期
	declare @workEndDate varchar(max)
	-- 工作日期
	declare @workDate varchar(max)
	-- 班次ID
	declare @bh varchar(max)

	--获取该日历年份
	select @workYear = calendar_year from bd_kq_work_calendar where id = @calendar_id;
	--下个月1号
	select @workBeginDate = CONVERT(varchar(7), dateadd(mm,1,getdate()) , 120) + '-01'
	--当年最后一天日期
	select @workEndDate = CONVERT(varchar(10), DATEADD(year, DATEDIFF(year, 0, DATEADD(year, 1, getdate())), -1) , 120)

	--  异常扑捉机制
		BEGIN TRY

			CREATE TABLE #user_serial(
				id INT IDENTITY,
				user_serial VARCHAR(20) COLLATE Chinese_PRC_CI_AS
			)

		-- 开启事务
			BEGIN TRAN

	    --判断是否是一年日期
			if datepart(yyyy,@workBeginDate) = datepart(yyyy,@workEndDate)
				BEGIN
			   -- 判断日历是否是大于0的
				 if @calendar_id>0
					BEGIN
						-- 判断是否是人员档案操作的
						if @lx = 0
							Begin
							  -- 获取人员属性的日历ID
								select @userCalendarId = isnull(user_work_calendar,0) from dt_user where user_serial = @user_serial_str
								if @userCalendarId is null
									Begin

										-- 要排班的数据
												 -- 定义游标
												 DECLARE cur_workDate CURSOR for select a.work_date,isnull(b.bh,'') as bh from bd_kq_work_calendar_detail a
left JOIN zt_banci b on a.shift_id = b.id where a.work_year = @workYear and a.parent_id = @userCalendarId  and CONVERT(varchar(10), a.work_date, 120 ) between @workBeginDate and @workEndDate

												 -- 打开游标
												OPEN cur_workDate

											  -- 开始循环游标变量--
												fetch next from cur_workDate into @workDate,@bh

												-- 提取游标数据，进行业务处理
												while @@fetch_status = 0   -- 如果上一次操作成功则继续循环
													BEGIN
														--排班
														exec bd_proc_schedule_pu_tong @user_serial_str,@workDate,@workDate,@bh,@ip,@operator,1
														FETCH  NEXT FROM cur_workDate INTO @workDate,@bh
													end

												-- 关闭游标
												CLOSE cur_workDate

												-- 释放游标
												deallocate cur_workDate

									end
								else
									BEGIN
										if @userCalendarId != @calendar_id
											Begin

												-- 要排班的数据
												 -- 定义游标
												 DECLARE cur_workDate CURSOR for select a.work_date,isnull(b.bh,'') as bh from bd_kq_work_calendar_detail a
left JOIN zt_banci b on a.shift_id = b.id where a.work_year = @workYear and a.parent_id = @calendar_id  and CONVERT(varchar(10), a.work_date, 120 ) between @workBeginDate and @workEndDate

												 -- 打开游标
												OPEN cur_workDate

											  -- 开始循环游标变量--
												fetch next from cur_workDate into @workDate,@bh

												-- 提取游标数据，进行业务处理
												while @@fetch_status = 0   -- 如果上一次操作成功则继续循环
													BEGIN
														--排班
														exec bd_proc_schedule_pu_tong @user_serial_str,@workDate,@workDate,@bh,@ip,@operator,1
														FETCH  NEXT FROM cur_workDate INTO @workDate,@bh
													end

												-- 关闭游标
												CLOSE cur_workDate

												-- 释放游标
												deallocate cur_workDate

											END
									END
							End
						Else 
							Begin

								-- 拆分用户编号保存临时表中
								insert into #user_serial
								select * from string_split(iif(isnull(@user_serial_str,'')='','',@user_serial_str),',');

								-- 修改人员工作日历
								update dt_user set user_work_calendar = @calendar_id where user_serial in(select user_serial from #user_serial);

								-- 要排班的数据
								-- 定义游标
								DECLARE cur_workDate CURSOR for select a.work_date,isnull(b.bh,'') as bh from bd_kq_work_calendar_detail a
left JOIN zt_banci b on a.shift_id = b.id where a.work_year = @workYear and a.parent_id = @calendar_id  and CONVERT(varchar(10), a.work_date, 120 ) between @workBeginDate and @workEndDate

								-- 打开游标
								OPEN cur_workDate

								-- 开始循环游标变量--
								fetch next from cur_workDate into @workDate,@bh

								-- 提取游标数据，进行业务处理
								while @@fetch_status = 0   -- 如果上一次操作成功则继续循环
									BEGIN
										--排班
										exec bd_proc_schedule_pu_tong @user_serial_str,@workDate,@workDate,@bh,@ip,@operator,1
										FETCH  NEXT FROM cur_workDate INTO @workDate,@bh
									end

								-- 关闭游标
								CLOSE cur_workDate

								-- 释放游标
								deallocate cur_workDate

							End
					end
				end
		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END;
			RAISERROR (@errStr, 16, 1);
		END CATCH

--  清除临时表
	drop table #user_serial

END;